N-of-N

The N-of-N functionality allows you to perform a series of nested loop queries (iterations) across multiple hierarchies.

This is a powerful tool used to answer classic analytical problems like "finding the top regions, and then for each region finding the top 5 products sold in each region". Pyramid includes an N-of-N engine because it is extremely difficult (near impossible) to generate the logic for this iterative query type using standard filters, calculations and sorts.

 

Note: This feature is not available in the Community Edition.

Note: The N-of-N wizard generates visual results only and does NOT generate any custom elements. If you wish to save N-of-N results, you can create a separate report for each N-of-N generation and then save it to ensure your results are preserved for future reference.

Adding N-of-N logic to your Report

To use N-of-N filtering the query must contain at least two hierarchies (and generally one measure). Then you can either use the "quick" N-of-N to apply default settings to your existing query or use the more advanced N-of-N wizard to configure and customize the logic. When you run the query, the series of nested loops are performed in succession, where each loop inherits the output data from the previous loop. Each loop is associated with a single row hierarchy and a single loop filter.

Visualizations

N-of-N is usually used in grid visualizations. However, there is no limitation on is application and it can be used on all visuals. Once you've configured an N-of-N filter, you can move the hierarchy chips to other drop zones in order to display the query results in a different way. For example, you can move one of the hierarchies to the Color zone

Quick N-of-N

The easiest way to add N-of-N logic is via the "quick" menu option from the drop zones. The option will be enabled if there are 2 or more 'blue' hierarchy chips in the zone. It will then apply a simple 'top 5 of the top 5' to the hierarchies using the currently deployed measure. If you then want to change these settings, you can open up the N-of-N wizard dialog (see below).

See the examples below for a step-by-step tutorial on how to use the Quick N-fo-N.

N-of-N Wizard

The N-of-N wizard dialog is available from the Query ribbon, in the 'functions' group. Clicking the button will allow you to add new, edit existing and remove N-of-N logic from your reports and queries. (This is how you can edit or refine the logic created from the quick menu item described above).

Te following panels are presented in the wizard dialog (more detail can be seen in the examples below):

Targets

In this panel select the hierarchies and combination of hierarchies that will be used to drive the selections. From this panel you can also elect to drive more than one sequence of N-of-N logic (click the blue plus sign to add each sequence). Note that:

  • Logic can be set across 2 or more hierarchies per sequence (i.e. N-of-N-of-N....).
  • Each hierarchy can be used in one sequence only

Settings

In the next panel, configure the filter settings for the selected hierarchy to drive the way the logic will be applied. They will control how the "n" elements for the chosen hierarchy will be materialized and used in the rest of the N-of-N iterative sequence.

A key aspect of the filtering definition used in the settings relates to the data point (or tuple) that drives the mathematical operations on the filter itself. This is set and defined using the hierarchy trees and subsequently picking specific elements from those trees.

  • For more details on how to use and configure settings see the filter dialog, which has identical options. This includes using parameterization to offer runtime changes to the settings.

Examples

The following examples will demonstrate how to use N-of-N and the different interfaces.